#!/bin/bash
# File Name: diff_grants.sh
# Author: moshan
# mail: mo_shan@yeah.net
# Created Time: 2019-02-22 16:53:17
# Function: 对比master与slave的用户权限是否一致
#########################################################################
:<<comment
变量注释:
db_name       : mysql权限库的库名，一般为'mysql';
mysql_user    : 连接mysql用户名;
mysql_passwd  : 连接mysql的密码;
mysql_port1   : 第一个节点的端口号;
mysql_port2   : 第二个节点的端口号;
log_file      : 日志文件;
mysql_host1   : 第一个节点的ip地址
mysql_host2   : 第二个节点的ip地址
注意：默认以第一个节点为参照，即程序会先获取到第一个节点的用户列表
comment
db_name="mysql"
mysql_user="mysql_user"
mysql_passwd="mysql_passwd"
mysql_port1="xxxx"
mysql_port2="xxxx"
log_file="diff.log"
mysql_host1="192.168.1.xxx"
mysql_host2="192.168.1.xxx"
 
#以下无需编辑修改
mysql_comm1="mysql -u${mysql_user} -p${mysql_passwd} -h${mysql_host1} -P${mysql_port1} ${db_name}"
mysql_comm2="mysql -u${mysql_user} -p${mysql_passwd} -h${mysql_host2} -P${mysql_port2} ${db_name}"
${mysql_comm1} -NBe "select 1;" >/dev/null 2>&1
if [ $? -ne 0 ]
then
        echo
        echo -e "\033[31m[$(date "+%F %H:%M:%S")] [ERROR] Can't connect to MySQL server on '${mysql_user}@${mysql_host1}' for mysql_port:${mysql_port1}\033[0m"
        echo
        exit 1
else
        >${log_file}
fi
for i in $(${mysql_comm1} -NBe "select concat(user,'@\'',host,'\'') from user" 2>/dev/null)
do
    md5sum_1=$(${mysql_comm1} -NBe "show grants for ${i};" 2>/dev/null|sort|md5sum)
    md5sum_2=$(${mysql_comm2} -NBe "show grants for ${i};" 2>/dev/null|sort|md5sum)
    if [ "${md5sum_1}x" == "${md5sum_2}x" ]
    then
        echo -e "\033[32m[$(date "+%F %H:%M:%S")] [OK] ${i}:ok"
    else
        echo -e "\033[31m[$(date "+%F %H:%M:%S")] [ERROR] ${i}:error"
        echo "${mysql_host1}:${mysql_port1}:$(${mysql_comm1} -NBe "show grants for ${i};" 2>/dev/null)" >> ${log_file}
        echo "${mysql_host2}:${mysql_port2}:$(${mysql_comm2} -NBe "show grants for ${i};" 2>/dev/null)" >> ${log_file}
    fi
done
echo
if [ -s "${log_file}" ]
then
        echo -e "\033[31m[$(date "+%F %H:%M:%S")] [ERROR] 存在部分用户权限不一致的情况，详情请查看${log_file}日志。\033[0m"
else
        echo -e "\033[32m[$(date "+%F %H:%M:%S")] [OK] 所有用户权限一致。\033[0m"
fi
echo
